#!/usr/bin/python
# coding=utf-8

import logging
import sys
import pymysql


# 加入日志
# 获取logger实例
logger = logging.getLogger("baseSpider")
# 指定输出格式
formatter = logging.Formatter('%(asctime)s\
              %(levelname)-8s:%(message)s')
# 文件日志
file_handler = logging.FileHandler("baseSpider.log")
file_handler.setFormatter(formatter)
# 控制台日志
console_handler = logging.StreamHandler(sys.stdout)
console_handler.setFormatter(formatter)
# 为logge添加具体的日志处理器
logger.addHandler(file_handler)
logger.addHandler(console_handler)
logger.setLevel(logging.INFO)


class DBHelper:
    # 构造函数
    def __init__(self, host='localhost',user='root',
                 pwd='password', db='bim', charset='utf8'):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db
        self.charset = charset
        self.conn = None
        self.cur = None

    # 连接数据库
    def connectDatabase(self):
        try:
            self.conn = pymysql.connect(self.host, self.user,
                                        self.pwd, self.db, charset='utf8')
        except:
            logger.error("connectDatabase failed")
            return False
        self.cur = self.conn.cursor()
        return True

    # 关闭数据库
    def close(self):
        # 如果数据打开，则关闭；否则没有操作
        if self.conn and self.cur:
            self.cur.close()
            self.conn.close()
        return True

    # 执行数据库的sq语句,主要用来做单条插入操作
    def executeOne(self, sql, params=None):
        # 连接数据库
        self.connectDatabase()
        try:
            if self.conn and self.cur:
                # 正常逻辑，执行sql，提交操作
                self.cur.execute(sql, params)
                self.conn.commit()
        except:
            logger.error("execute failed: " + sql)
            logger.error("params: " + params)
            return False
        finally:
            self.close()
        return True

        # 执行数据库的sq语句,主要用来做多条插入操作

    def executemany(self, sql, data=None):
        # 连接数据库
        self.connectDatabase()
        try:
            if self.conn and self.cur:
                # 正常逻辑，执行sql，提交操作
                self.cur.executemany(sql, data)
                self.conn.commit()
        except:
            logger.error("execute failed: " + sql)
            logger.error("params: " + data)
            return False
        finally:
            self.close()
        return True

    # 用来查询表数据，返回单条数据
    def fetchOne(self, sql, params=None):
        # 连接数据库
        self.connectDatabase()
        try:
            if self.conn and self.cur:
                # 正常逻辑，执行sql，提交操作
                self.cur.execute(sql, params)
        except:
            logger.error("execute failed: " + sql)
            logger.error("params: " + params)
            return False
        finally:
            self.close()
        return self.cur.fetchone()



    # 用来查询表数据，返回单条数据
    def fetchAll(self, sql, params=None):
        # 连接数据库
        self.connectDatabase()
        try:
            if self.conn and self.cur:
                # 正常逻辑，执行sql，提交操作
                self.cur.execute(sql, params)
        except:
            logger.error("execute failed: " + sql)
            logger.error("params: " + params)
            return False
        finally:
            self.close()
        return self.cur.fetchall()

    # 执行数据库的sq语句,主要用来做单条插入操作
    def updataOne(self, sql, params=None):
        # 连接数据库
        self.connectDatabase()
        try:
            if self.conn and self.cur:
                # 正常逻辑，执行sql，提交操作
                self.cur.execute(sql, params)
                self.conn.commit()
        except:
            logger.error("execute failed: " + sql)
            logger.error("params: " + params)
            return False
        finally:
            self.close()
        return True
